﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace SME.Food.Surperise.DBHelper
{
    //查询易盛餐饮数据库数据所用
   public abstract class DbHelper
    {
       private static SqlConnection conn = null;
       //获取配置文件中写在ConnectionStrings标签内的连接字符串
       static string connstring = System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
       public DbHelper()
       {
           GetConn();
       }
       
      
       public static SqlConnection GetConn()
       {
           if (conn == null)
           {

               conn = new SqlConnection(connstring);
           }
           if (conn.State == ConnectionState.Closed)
           {
               conn.Open();
           }
           return conn;
       }

       public static int GetMaxID(string FieldName, string TableName)
       {
           string strsql = "select max(" + FieldName + ")+1 from " + TableName;
           object obj = GetSingle(strsql);
           if (obj == null)
           {
               return 1;
           }
           else
           {
               return int.Parse(obj.ToString());
           }
       }

       public static bool Exists(string strSql)
       {
           object obj = GetSingle(strSql);
           int cmdresult;
           if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
           {
               cmdresult = 0;
           }
           else
           {
               cmdresult = int.Parse(obj.ToString());
           }
           if (cmdresult == 0)
           {
               return false;
           }
           else
           {
               return true;
           }
       }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public static DataSet Query(string SQLString)
        {
                DataSet ds = new DataSet();
                try
                {
                   
                    SqlDataAdapter command = new SqlDataAdapter(SQLString,  conn);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
         }
       

        public static object ExecuteScalar(string sql)
        {
            GetConn();
            SqlCommand cmd = new SqlCommand(sql, conn);
            return cmd.ExecuteScalar();

        }

        /// <summary>
        /// 查询单列数据
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>返回集合对像</returns>

        public static ArrayList QueryList(string sql)
        {
            GetConn();
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            ArrayList al = new ArrayList();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                al.Add(ds.Tables[0].Rows[i][0]);
            }
            return al;
        }

        public static int ExcuteNoQuery(string sql)
        {
            GetConn();
            SqlCommand cmd = new SqlCommand(sql, conn);
            int k = cmd.ExecuteNonQuery();
            return k;
        }

        public static SqlDataReader ExecuteQuery(string sql)
        {
            GetConn();
            SqlCommand cmd = new SqlCommand();//sql语句命令执行对象
            cmd.Connection = conn;
            cmd.CommandText = sql;//待执行的sql脚本
            SqlDataReader dr = cmd.ExecuteReader();//执行结果放入datareader
            return dr;
        }
        public static int ExcuteNoQuery(string sql, SqlParameter[] paras)
        {
            GetConn();
            SqlCommand cmd = new SqlCommand();//sql语句命令执行对象
            cmd.Connection = conn;
            cmd.CommandText = sql;

            foreach (var para in paras)
            {
                cmd.Parameters.Add(para);
            }

            return cmd.ExecuteNonQuery();
        }

        /// <summary>
        /// 执行一条计算查询结果语句，返回查询结果（object）。
        /// </summary>
        /// <param name="SQLString">计算查询结果语句</param>
        /// <returns>查询结果（object）</returns>
        public static object GetSingle(string SQLString)
        {

            using (SqlCommand cmd = new SqlCommand(SQLString, conn))
            {
                try
                {

                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    conn.Close();
                    throw e;
                }
            }

        }
    }
}
